package com.lan.app.dao;

import com.lan.app.repository.Tb;
import com.lan.support.page.PageUtils;
import com.lan.support.page.Paging;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class TbDao {

    private static final String tbName = "tb_tb";

    private static final String colSQL = "id,zh,mn,remark,state,is_del,create_time,update_time";

    private static final String insertSQL = "INSERT INTO " + tbName + "(zh,mn,remark,state,is_del,create_time,update_time) VALUES(:zh,:mn,:remark,:state,:isDel,:createTime,:updateTime)";

    private static final String delSQL = "DELETE FROM " + tbName + " WHERE id =:id";

    private static final String selectSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE id=:id";

    private static final String isDelSQL = "UPDATE " + tbName + " SET is_del=:isDel,update_time=:updateTime WHERE id=:id";

    private static final String selectIdListSQL = "SELECT " + colSQL + " FROM " + tbName + "  WHERE id IN(:itemList)";

    private static final String selectALLSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE is_del=1";


    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public int save(Tb tb) {
        SqlParameterSource ps = new BeanPropertySqlParameterSource(tb);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(insertSQL, ps, keyHolder);
        tb.setId(keyHolder.getKey().intValue());
        return keyHolder.getKey().intValue();
    }

    public int del(Integer id) {
        Map<String, Object> param = new HashMap<>();
        param.put("id", id);
        return namedParameterJdbcTemplate.update(delSQL, param);
    }

    public Integer modifyDel(Integer id, Integer isDel, Date curDate) {
        Map<String, Object> param = new HashMap();
        param.put("id", id);
        param.put("isDel", isDel);
        param.put("updateTime", curDate);
        return namedParameterJdbcTemplate.update(isDelSQL, param);
    }

    public int updateById(Tb tb) {
        StringBuilder sb = new StringBuilder("UPDATE " + tbName + " SET update_time=:updateTime");
        Map<String, Object> param = new HashMap();
        param.put("updateTime", new Date());
        if (StringUtils.isNotBlank(tb.getZh())) {
            sb.append(",zh=:zh");
            param.put("zh", tb.getZh());
        }
        if (StringUtils.isNotBlank(tb.getMn())) {
            sb.append(",mn=:mn");
            param.put("mn", tb.getMn());
        }
        if (StringUtils.isNotBlank(tb.getRemark())) {
            sb.append(",remark=:remark");
            param.put("remark", tb.getRemark());
        }
        sb.append(" WHERE id=:id");
        param.put("id", tb.getId());
        return namedParameterJdbcTemplate.update(sb.toString(), param);
    }

    public Tb findById(Integer id) {
        Map<String, Object> param = new HashMap();
        param.put("id", id);
        List<Tb> list = namedParameterJdbcTemplate.query(selectSQL, param, new Mapper());
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    public List<Tb> findByIdList(List<Integer> itemList) {
        Map<String, Object> param = new HashMap<>();
        param.put("itemList", itemList);
        return namedParameterJdbcTemplate.query(selectIdListSQL, param, new Mapper());
    }

    public Integer countByPage(Tb tb, Paging page) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(id) FROM " + tbName + " WHERE is_del=1 ");
        Map<String, Object> param = new HashMap();
        where(sb, param, tb);
        PageUtils.page(page, param, sb, false);
        return namedParameterJdbcTemplate.queryForObject(sb.toString(), param, Integer.class);
    }

    public List<Tb> findByPage(Tb tb, Paging page) {
        StringBuilder sb = new StringBuilder("SELECT " + colSQL + " FROM " + tbName + " WHERE is_del=1 ");
        Map<String, Object> param = new HashMap();
        where(sb, param, tb);
        PageUtils.page(page, param, sb, false);
        PageUtils.page(page, param, sb);
        return namedParameterJdbcTemplate.query(sb.toString(), param, new Mapper());
    }

    private void where(StringBuilder sb, Map<String, Object> param, Tb tb) {
        if (StringUtils.isNotBlank(tb.getZh())) {
            param.put("zh", tb.getZh());
            sb.append(" AND zh=:zh");
        }
        if (StringUtils.isNotBlank(tb.getMn())) {
            param.put("mn", tb.getMn());
            sb.append(" AND mn=:mn");
        }
        if (tb.getState() != null) {
            param.put("state", tb.getState());
            sb.append(" AND state=:state");
        }
    }

    public List<Tb> getALL() {
        Map<String, Object> param = new HashMap();
        return namedParameterJdbcTemplate.query(selectALLSQL, param, new Mapper());
    }

    class Mapper implements RowMapper<Tb> {
        @Override
        public Tb mapRow(ResultSet rs, int i) throws SQLException {
            Tb item = new Tb();
            item.setId(rs.getInt("id"));
            item.setZh(rs.getString("zh"));
            item.setMn(rs.getString("mn"));
            item.setRemark(rs.getString("remark"));
            item.setState(rs.getInt("state"));
            item.setIsDel(rs.getInt("is_del"));
            item.setCreateTime(rs.getTimestamp("create_time"));
            item.setUpdateTime(rs.getTimestamp("update_time"));
            return item;
        }
    }

}
